﻿using Sunny.UI;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace 渐变
{
    public partial class userManager : Form
    {
        public userManager()
        {
            InitializeComponent();
        }

        DataTable dt = null;

        public void loadMsg2()
        {
            string sql = "select * from uuser";
            dt = new DataTable();
            bunifuCustomDataGrid1.AutoGenerateColumns = false;
            dt = DBHelper.ExecuteQuery(sql);
            bunifuCustomDataGrid1.DataSource = dt;
        }
        public void loadMsg()
        {
            /* string sql = "select * from uuser";
             dt = new DataTable();
             bunifuCustomDataGrid1.AutoGenerateColumns = false;
             dt = DBHelper.ExecuteQuery(sql);
             bunifuCustomDataGrid1.DataSource = dt;*/

            this.source = new DataSet();
            this.pageSize = 10;
            this.CurrentPage = 1;
            string connstring = System.Configuration.ConfigurationSettings.AppSettings["connstring"].ToString();
            SqlConnection conn = new SqlConnection(connstring);
            conn.Open();
            string sql = "select * from uuser";
            bunifuCustomDataGrid1.AutoGenerateColumns = false;
            DataAdapter adapter = new SqlDataAdapter(sql, conn);
            adapter.Fill(source);

            BindPage();
        }
        private void Userpanel_Paint(object sender, PaintEventArgs e)
        {
            loadMsg();
            loadMsg2();
        }

        private void bunifuCustomDataGrid1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            //获取选中行的索引
            int numHang = this.bunifuCustomDataGrid1.CurrentRow.Index;
            //获取选中行 中 列的索引
            int numLie = this.bunifuCustomDataGrid1.CurrentCell.ColumnIndex;

            //获取选中列的Name
            //string LieName = this.bunifuCustomDataGrid1.Columns[numLie].Name;

            //获得指定行列修改过后值
            string uusername = this.bunifuCustomDataGrid1.Rows[numHang].Cells["username"].Value.ToString();
            string uphone = this.bunifuCustomDataGrid1.Rows[numHang].Cells["phone"].Value.ToString();
            string upassword = this.bunifuCustomDataGrid1.Rows[numHang].Cells["password"].Value.ToString();
            string uaddress = this.bunifuCustomDataGrid1.Rows[numHang].Cells["address"].Value.ToString();
            //获得修改行的主键
            int userId = int.Parse(this.bunifuCustomDataGrid1.Rows[numHang].Cells["uid"].Value.ToString());

            string sql = "update uuser set uUsername=@uname,uPassword=@uPassword,uPhone=@uPhone,uAddress=@uAddress where uId = @uid";
            SqlParameter[] ps =
           {
                new SqlParameter("@uid",userId),
                new SqlParameter("@uname",uusername),
                new SqlParameter("@uPhone", uphone),
                new SqlParameter("@uPassword", upassword),
                new SqlParameter("@uAddress", uaddress)
        };
            int fin = DBHelper.ExecuteNonQuery(sql, ps);
            if (fin > 0)
            {
                this.ShowSuccessTip("修改成功");
            }
            else
            {
                this.ShowErrorTip("修改失败");
            }
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            DialogResult RSS = MessageBox.Show(this, "确定要删除选中行数据码？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
            switch (RSS)
            {
                case DialogResult.Yes:
                    for (int i = this.bunifuCustomDataGrid1.SelectedRows.Count; i > 0; i--)
                    {
                        int ID = Convert.ToInt32(bunifuCustomDataGrid1.SelectedRows[i - 1].Cells[0].Value);
                        bunifuCustomDataGrid1.Rows.RemoveAt(bunifuCustomDataGrid1.SelectedRows[i - 1].Index);
                        //使用获得的ID删除数据库的数据
                        string sql = "delete  from uuser where uId=@uId";
                        int s = DBHelper.ExecuteNonQuery(sql, new SqlParameter("@uid", ID));
                        if (s != 0)
                        {
                            MessageBox.Show("成功删除选中行数据！");
                        }
                    }
                    break;
                case DialogResult.No:
                    break;
            }
        }

        /// <summary>
        /// 导出报表为Csv
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <param name="strFilePath">物理路径</param>
        /// <param name="tableheader">表头</param>
        /// <param name="columname">字段标题,逗号分隔</param>
        public static bool dt2csv(DataTable dt, string strFilePath, string tableheader, string columname)
        {
            
            try
            {
                string strBufferLine = "";
                StreamWriter strmWriterObj = new StreamWriter(strFilePath, false, System.Text.Encoding.UTF8);
                strmWriterObj.WriteLine(tableheader);
                strmWriterObj.WriteLine(columname);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    strBufferLine = "";
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        if (j > 0)
                            strBufferLine += ",";
                        strBufferLine += dt.Rows[i][j].ToString();
                    }
                    strmWriterObj.WriteLine(strBufferLine);
                }
                strmWriterObj.Close();
                return true;
            }
            catch
            {
                return false;
            }
        }
      
        private void btnDaochu_Click(object sender, EventArgs e)
        {
            string path = AppDomain.CurrentDomain.BaseDirectory + @"" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".csv";
            if (dt2csv(dt, path, "用户信息", "编号,用户名,密码,电话,地址"))
            {
                MessageBox.Show("导出成功,文件位置:" + path);
            }
            else
            {
                MessageBox.Show("导出失败");
            }
        }

        int pageCount = 0;
        public int PageCount
        {
            get
            {
                if (source != null)
                {
                    pageCount = (int)Math.Ceiling(source.Tables[0].Rows.Count * 1.0 / pageSize);
                }
                return pageCount;
            }
        }

        DataSet source;//用于存储数据库中的查询出来的数据
        int pageSize = 5;//每页条目数
        int currentPage = 1;
        /// <summary>
        /// 当前页
        /// </summary>
        public int CurrentPage
        {
            get { return currentPage; }
            set
            {
                if (value <= 1)
                {
                    value = 1;
                    this.bdFirstItem.Enabled = false;
                    this.bdPreviousItem.Enabled = false;
                    this.bdNextItem.Enabled = true;
                    this.bdLastItem.Enabled = true;
                }
                else if (value >= PageCount)
                {
                    value = pageCount;
                    this.bdFirstItem.Enabled = true;
                    this.bdPreviousItem.Enabled = true;
                    this.bdNextItem.Enabled = false;
                    this.bdLastItem.Enabled = false;
                }
                else
                {
                    this.bdFirstItem.Enabled = true;
                    this.bdPreviousItem.Enabled = true;
                    this.bdNextItem.Enabled = true;
                    this.bdLastItem.Enabled = true;

                }
                currentPage = value;
            }
        }
        int currentRow;
        /// <summary>
        /// 每页首行索引
        /// </summary>
        public int CurrentRow
        {
            get
            {
                if (CurrentPage == 1)
                {
                    currentRow = 0;
                }
                else
                {
                    currentRow = (CurrentPage - 1) * pageSize;
                }
                return currentRow;
            }
        }
        int max = 0;
        /// <summary>
        /// 每页行数最大值索引
        /// </summary>
        public int Max
        {
            get
            {
                if (CurrentPage == pageCount)
                {
                    max = source.Tables[0].Rows.Count;
                }
                else
                {
                    max = pageSize * CurrentPage;
                }
                return max;
            }
        }
        /// <summary>
        /// 绑定combobox中的页码
        /// </summary>
        private void BindPage()
        {
            this.bdCountItem.Text = PageCount.ToString();
            this.tspcbo.Items.Clear();
            for (int i = 1; i <= pageCount; i++)
            {
                this.tspcbo.Items.Add("第" + i + "页");
            }
            this.tspcbo.SelectedIndex = 0;
        }
        /// <summary>
        /// 分页绑定数据
        /// </summary>
        private void BindSourceFY()
        {
            DataTable dt = new DataTable();
            //dt.TableName = "tb";//这个可以没有
            //下面这个循环比较有用，没有这个循环直接绑定的话datagridview是不显示数据的
            //原因是在dt这个对象里面那个columns集合是空，所以这个datagridview没有办法自动绑定 列的名字可以根据自己的需要添加想要的名字
            for (int i = 0; i < source.Tables[0].Columns.Count; i++)
            {
                dt.Columns.Add(source.Tables[0].Columns[i].ToString());
            }
            for (int i = CurrentRow; i < Max; i++)
            {
                dt.ImportRow(source.Tables[0].Rows[i]);
            }
            this.bunifuCustomDataGrid1.DataSource = dt;
            //this.dgv.DataMember = "tb";

        }

        private void bdFirstItem_Click(object sender, EventArgs e)
        {
            this.tspcbo.SelectedIndex = 0;
        }

        private void bdPreviousItem_Click(object sender, EventArgs e)
        {
            this.tspcbo.SelectedIndex = CurrentPage - 2;
        }

        private void tspcbo_SelectedValueChanged(object sender, EventArgs e)
        {
            CurrentPage = this.tspcbo.SelectedIndex + 1;
            this.bdPositionItem.Text = CurrentPage.ToString();
            BindSourceFY();
        }

        private void bdNextItem_Click(object sender, EventArgs e)
        {
            this.tspcbo.SelectedIndex = CurrentPage;
        }

        private void bdLastItem_Click(object sender, EventArgs e)
        {
            this.tspcbo.SelectedIndex = PageCount - 1;
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            addUserForm userForm = new addUserForm();
            userForm.Show();
        }

        private void userManager_Load(object sender, EventArgs e)
        {
            this.Dock = DockStyle.Fill;
        }
    }
}
